﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DataControl;
using System.Data;
using System.Text;
using ATW.Bus;
using System.Collections;


namespace ATW.MT.BUS
{
    public class bus_T_production
    {
        DataAccess da = null;
        public bus_T_production()
        {
            da = new DataAccess();
        }
        #region 方法
        /// <summary>
        /// 分页查询产品
        /// </summary>
        /// <param name="name"></param>
        /// <param name="status"></param>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public DataTable ProductionPagerQuery(string name, string status, int page, int pagesize)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"select P.ProductionID,
                                       s.num,
	                                   PM.[Desc],
	                                   imgUrl,
	                                   rate,
	                                   P.rowstatus,
	                                   CONVERT(nvarchar(20),P.create_time,111) create_time from T_Production P
	                                   left join (select ProductionID,COUNT(*) num from T_Production_MultiLan group by ProductionID)s 
                                                                on s.ProductionID=P.ProductionID
	                                   left join T_Production_MultiLan PM on P.ProductionID=PM.ProductionID and PM.LanId='zh' where 1=1");
            if (!String.IsNullOrEmpty(name))
            {
                sql.Append(" and PM.[Desc] like '%" + name + "%'");
            }
            if (!String.IsNullOrEmpty(status))
            {
                sql.Append(" and P.rowstatus='" + status + "'");
            }
            return da.PageSize(sql.ToString(), page, pagesize, "ProductionID");
        }
        /// <summary>
        /// 产品查询
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public DataTable ProductionQuery(string uid)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"select P.ProductionID,PM.[desc],ISNULL(AP.tauloginID,'') isused from T_Production P
                                left join T_Production_MultiLan PM on P.ProductionID=PM.ProductionID and LanId='zh'
                                left join T_Appuser_Production AP on P.ProductionID=AP.ProductionID and AP.tauloginID='{0}'
                                where 1=1", uid);
            return da.ExecuteDataTable(sql.ToString());
        }
        /// <summary>
        /// 产品明细查询
        /// </summary>
        /// <param name="productionID"></param>
        /// <returns></returns>
        public DataTable ProductionSingleQuery(string productionID)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"select P.ProductionID,
	                   PM.[Desc],
	                   imgUrl,
	                   rate,
	                   P.rowstatus,
	                   CONVERT(nvarchar(20),P.create_time,111) create_time from T_Production P
	                   left join T_Production_MultiLan PM on P.ProductionID=PM.ProductionID and PM.LanId='zh' where P.ProductionID='{0}'", productionID);
            return da.ExecuteDataTable(sql.ToString());
        }
        /// <summary>
        /// 新增产品
        /// </summary>
        /// <param name="name"></param>
        /// <param name="imgUrl"></param>
        /// <returns></returns>
        public bool ProductionAdd(string name, string rate, string imgUrl)
        {
            string proid = GetNewID();
            ArrayList al = new ArrayList();
            al.Add(" insert into T_Production (ProductionID,imgUrl,rate)values('" + proid + "','" + imgUrl + "','" + rate + "')");
            al.Add(" insert into T_Production_MultiLan(ProductionID,LanId,[Desc])values('" + proid + "','zh','" + name + "')");
            return da.ExecuteArrayList(al);
        }
        /// <summary>
        /// 编辑产品
        /// </summary>
        /// <param name="name"></param>
        /// <param name="imgUrl"></param>
        /// <param name="proid"></param>
        /// <returns></returns>
        public bool ProductionEdit(string name, string imgUrl, string rate, string proid)
        {
            ArrayList al = new ArrayList();
            al.Add(" update T_Production set imgUrl='" + imgUrl + "',rate='" + rate + "' where ProductionID='" + proid + "'");
            al.Add(" update T_Production_MultiLan set [desc]='" + name + "' where ProductionID='" + proid + "' and lanid='zh'");
            return da.ExecuteArrayList(al);
        }

        /// 更新产品状态
        /// </summary>
        /// <param name="pid"></param>
        /// <param name="status">0正常 2删除</param>
        /// <returns></returns>
        public int ChangeStatus(string pid, string status)
        {
            string sql = "update T_Production set rowstatus='" + status + "' where productionid='" + pid + "'";
            return da.ExcuteNoQuery(sql);
        }
        /// <summary>
        /// 查询产品描述
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public DataTable ProductionDescQuery(string pid)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"select pm.ProductionID,pm.[Desc],l.LanName from T_Production_MultiLan pm 
                    left join D_Language l on pm.LanId=l.LanId where pm.rowstatus<>'2' and pm.productionid='{0}'", pid);
            return da.ExecuteDataTable(sql.ToString());
        }
        /// <summary>
        /// 新增描述
        /// </summary>
        /// <param name="pid"></param>
        /// <param name="lanid"></param>
        /// <param name="desc"></param>
        /// <returns></returns>
        public int ProductionDescAdd(string pid, string lanid, string desc)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"insert into T_Production_MultiLan(productionid,lanid,[desc])values('{0}','{1}','{2}')", pid, lanid, desc);
            return da.ExcuteNoQuery(sql.ToString());
        }
        /// <summary>
        /// 产品描述是否存在
        /// </summary>
        /// <param name="pid"></param>
        /// <param name="lanid"></param>
        /// <returns></returns>
        public bool isProductionDescExist(string pid, string lanid)
        {
            string sql = "select * from T_Production_MultiLan where productionid='" + pid + "' and lanid='" + lanid + "' and rowstatus<>'2'";
            DataTable dt = da.ExecuteDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// 产品明细分页查询
        /// </summary>
        /// <param name="pid">类型id</param>
        /// <param name="MID_b">阀体材质</param>
        /// <param name="mid_S">阀杆材质</param>
        /// <param name="mid_i">阀瓣材质</param>
        /// <param name="SId">口径</param>
        /// <param name="pressid">压力等级</param>
        /// <param name="connid">连接方式</param>
        /// <param name="mid_a">阀座材质</param>
        /// <param name="designID">设计等级</param>
        /// <param name="franid">法兰标准</param>
        /// <param name="structId">结构等级</param>
        /// <param name="sealID">密封等级</param>
        /// <param name="mid_f">填料</param>
        /// <param name="type">类型（铸件、锻件）</param>
        /// <param name="status">状态 0正常 2删除</param>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public DataTable PDetailQuery(string pid, string MID_b, string mid_S, string mid_i, string SId,
                string pressid, string connid, string mid_a, string designID, string franid,
                string structId, string sealID, string type, string mid_f, string status,
                int page, int pagesize)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"select PM.[Desc] typeName,
	                           PDM.[Desc] detailName,
	                           m1.Material_code body,
	                           m2.Material_code stick,
	                           m3.Material_code [inner],
	                           m4.Material_code base,
	                           s.Size_Code_C size_c,
	                           s.Size_Code_S size_s,
	                           pl.Pressure_Code_C p_c,
	                           pl.Pressure_Code_S p_s,
	                           co.[Desc] conn,
	                           DL.[Desc] Design,
	                           FL.[Desc] Fran,
	                           Struct.[Desc] Struct,
	                           Seal.LevelType SealType,
	                           Seal.[Level] SealLevel,
	                           PD.args08 [type],
	                           m5.Material_code fill,
	                           PD.rate,
	                           PD.create_time,
                               PD.rowstatus
	                            from T_ProductionDetail PD
                        left join T_Production P on P.ProductionID=PD.ProductionID
                        left join (select * from T_Production_MultiLan where LanId='zh') PM on P.ProductionID=PM.ProductionID
                        left join (select * from T_ProductionDetail_MultiLan where LanId='zh') PDM on PD.ProDetaidID=PDM.ProDetaidID
                        left join C_Material m1 on m1.MID=PD.MID_B
                        left join C_Material m2 on m2.MID=PD.MID_S
                        left join C_Material m3 on m3.MID=PD.MID_I
                        left join C_Material m4 on m4.MID=PD.args02
                        left join C_Size s on s.[SID]=PD.[SID]
                        left join C_PressureLevel pl on pl.PressureID=PD.PressureID
                        left join (select c.ConnID,i1.[Desc] from C_Connection c 
					                        left join 
					                        (select [desc],ConnID from C_Connection_MultiLan where LanId='zh')i1 on i1.ConnID=c.ConnID) co on PD.ConnID=co.ConnID
                        left join C_DesignLevel DL on PD.args04=DL.DesignLevelID
                        left join C_FranLevel FL on PD.args05=FL.FranLevelID
                        left join C_StructLevel Struct on PD.args06=Struct.StructLevelID
                        left join C_SealLevel Seal on PD.args07=Seal.SealLevelID
                        left join C_Material m5	on PD.args09=m5.MID where 1=1");
            if (!String.IsNullOrEmpty(pid))
            {
                sql.Append(" and PD.ProductionID='" + pid + "'");
            }
            if (!String.IsNullOrEmpty(MID_b))
            {
                sql.Append(" and PD.MID_B='" + MID_b + "'");
            }
            if (!String.IsNullOrEmpty(mid_S))
            {
                sql.Append(" and PD.MID_S='" + mid_S + "'");
            }
            if (!String.IsNullOrEmpty(mid_i))
            {
                sql.Append(" and PD.MID_I ='" + mid_i + "'");
            }
            if (!String.IsNullOrEmpty(SId))
            {
                sql.Append(" and PD.SID='" + SId + "'");
            }
            if (!string.IsNullOrEmpty(pressid))
            {
                sql.Append(" and PD.PressureID='" + pressid + "'");
            }
            if (!string.IsNullOrEmpty(connid))
            {
                sql.Append(" and PD.ConnID='" + connid + "'");
            }
            if (!string.IsNullOrEmpty(mid_a))
            {
                sql.Append(" and PD.args02 ='" + mid_a + "'");
            }
            if (!string.IsNullOrEmpty(designID))
            {
                sql.Append(" and PD.args04='" + designID + "'");
            }
            if (!string.IsNullOrEmpty(franid))
            {
                sql.Append(" and PD.args05='" + franid + "'");
            }
            if (!string.IsNullOrEmpty(structId))
            {
                sql.Append(" and PD.args06='" + structId + "'");
            }
            if (!string.IsNullOrEmpty(sealID))
            {
                sql.Append(" and PD.args07='" + sealID + "'");
            }
            if (!string.IsNullOrEmpty(type))
            {
                sql.Append(" and PD.args08='" + type + "'");
            }
            if (!string.IsNullOrEmpty(mid_f))
            {
                sql.Append(" and PD.args09='" + mid_f + "'");
            }
            if (!string.IsNullOrEmpty(status))
            {
                sql.Append(" and rowstatus='" + status + "'");
            }
            return da.PageSize(sql.ToString(), page, pagesize, "ProDetaidID");
        }

        private string GetNewID()
        {
            string sql = "select newid()";
            object o = da.ExecuteScalar(sql);
            if (o != null && o.ToString() != "")
            {
                return o.ToString();
            }
            return "";
        }
        public bool DelProduct(string uid, string pid)
        {
            ArrayList AL = new ArrayList();
            AL.Add("delete from T_Appuser_Production where tauloginID='" + uid + "' and ProductionID='" + pid + "'");
            AL.Add("delete from T_Appuser_Material where tauloginID='" + uid + "' and ProductionID='" + pid + "'");
            AL.Add("delete from T_Appuser_Pressure where tauloginID='" + uid + "' and ProductionID='" + pid + "'");
            AL.Add("delete from T_Appuser_Size where tauloginID='" + uid + "' and ProductionID='" + pid + "'");
            AL.Add("delete from T_Appuser_Temperature where tauloginID='" + uid + "' and ProductionID='" + pid + "'");
            AL.Add("delete from T_Appsuer_Seal where tauloginID='" + uid + "' and ProductionID='" + pid + "'");
            return da.ExecuteArrayList(AL);
        }
        #endregion
    }
}